﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using TracNghiemTinHocDaiCuong.Common;
using TracNghiemTinHocDaiCuong.ThucThe;

namespace TracNghiemTinHocDaiCuong.DAO
{
    public class DeThiDAO
    {
        public static bool TaoDeThi(DeThi deThi)
        {
            string query = string.Format("SELECT TOP {0} MaCH FROM CauHoi WHERE MaCD=@MaCD ORDER BY NEWID()", deThi.SoCauHoi);
            var parameter = new Collection<SqlParameter>
            {
                //new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@MaCD", deThi.MaCD)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            string queryThem = string.Format("INSERT INTO DeThi_CauHoi(MaDT, MaCH) VALUES(@MaDT, @MaCH)");
            
            while (read.Read())
            {
                var parameterThem = new Collection<SqlParameter>
                {
                    new SqlParameter("@MaDT", deThi.MaDT),
                    new SqlParameter("@MaCH", Methods.ConvertNumber(read["MaCH"].ToString()))
                };
                SqlHelper.ExecuteNonQuery(queryThem, parameterThem);
            }
            return true;
        }

        public static bool TaoDeThiMoi(DeThi deThi)
        {
            string query = string.Format("INSERT INTO DeThi(TenDT, MaCD, DoKho, SoCauHoi, ThoiGian, NgayTao, DaThi) " +
            "VALUES(@TenDT, @MaCD, @DoKho, @SoCauHoi, @ThoiGian, @NgayTao, @DaThi) " +
            "SELECT @@IDENTITY AS MaDe");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenDT", deThi.TenDT),
                new SqlParameter("@MaCD", deThi.MaCD),
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@ThoiGian", deThi.ThoiGian),
                new SqlParameter("@NgayTao", deThi.NgayTao),
                new SqlParameter("@DaThi", false)
            };
            IDataReader readMaDe = SqlHelper.ExecuteReader(query, parameter);
            int maDe = 0;
            while(readMaDe.Read())
            {
                maDe = Methods.ConvertNumber(readMaDe["MaDe"].ToString());
            }
            string queryTaoDe = string.Format("SELECT TOP {0} MaCH FROM CauHoi WHERE MaCD=@MaCD AND DoKho=@DoKho ORDER BY NEWID()", deThi.SoCauHoi);
            var parameterTaoDe = new Collection<SqlParameter>
            {
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@MaCD", deThi.MaCD)
            };
            IDataReader readTaoDe = SqlHelper.ExecuteReader(queryTaoDe, parameterTaoDe);
            string queryThem = string.Format("INSERT INTO DeThi_CauHoi(MaDT, MaCH) VALUES(@MaDT, @MaCH)");

            while (readTaoDe.Read())
            {
                var parameterThem = new Collection<SqlParameter>
                {
                    new SqlParameter("@MaDT", maDe),
                    new SqlParameter("@MaCH", Methods.ConvertNumber(readTaoDe["MaCH"].ToString()))
                };
                SqlHelper.ExecuteNonQuery(queryThem, parameterThem);
            }
            return true;
        }

        public static bool TaoDeThiTongHopMoi(DeThi deThi)
        {
            string query = string.Format("INSERT INTO DeThi(TenDT, DoKho, SoCauHoi, ThoiGian, NgayTao, DaThi) " +
            "VALUES(@TenDT, @DoKho, @SoCauHoi, @ThoiGian, @NgayTao, @DaThi) " +
            "SELECT @@IDENTITY AS MaDe");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenDT", deThi.TenDT),
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@ThoiGian", deThi.ThoiGian),
                new SqlParameter("@NgayTao", deThi.NgayTao),
                new SqlParameter("@DaThi", false)
            };
            IDataReader readMaDe = SqlHelper.ExecuteReader(query, parameter);
            int maDe = 0;
            while(readMaDe.Read())
            {
                maDe = Methods.ConvertNumber(readMaDe["MaDe"].ToString());
            }
            string queryTaoDe = string.Format("SELECT TOP {0} MaCH FROM CauHoi WHERE DoKho=@DoKho ORDER BY NEWID()", deThi.SoCauHoi);
            var parameterTaoDe = new Collection<SqlParameter>
            {
                new SqlParameter("@DoKho", deThi.DoKho)
            };
            IDataReader readTaoDe = SqlHelper.ExecuteReader(queryTaoDe, parameterTaoDe);
            string queryThem = string.Format("INSERT INTO DeThi_CauHoi(MaDT, MaCH) VALUES(@MaDT, @MaCH)");

            while (readTaoDe.Read())
            {
                var parameterThem = new Collection<SqlParameter>
                {
                    new SqlParameter("@MaDT", maDe),
                    new SqlParameter("@MaCH", Methods.ConvertNumber(readTaoDe["MaCH"].ToString()))
                };
                SqlHelper.ExecuteNonQuery(queryThem, parameterThem);
            }
            return true;
        }

        

        public static IList<DeThi> TimKiem(string tenDT, string range)
        {
            string query = string.Format("SELECT * " +
                                            "FROM DeThi ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenDT", tenDT)
            };
            IList<DeThi> danhSachDeThi = new List<DeThi>();
            if (tenDT != "")
            {
                query += string.Format("WHERE TenDT LIKE '%'+@TenDT+'%' " +
                " ORDER BY TenDT");
                return DanhSachDeThi(danhSachDeThi, query, parameter);
            }
            else
            {
                query += string.Format("WHERE TenDT LIKE @Range " +
                " ORDER BY TenDT");
                var par = new Collection<SqlParameter>
                {
                    new SqlParameter("@Range", range)
                };
                return DanhSachDeThi(danhSachDeThi, query, par);
            }
        }

        public static IList<DeThi> DanhSachDeThi(IList<DeThi> danhSachDeThi,
            string query, Collection<SqlParameter> parameter)
        {
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            while (read.Read())
            {
                danhSachDeThi.Add(DoiTuong(read));
            }
            return danhSachDeThi;
        }

        public static DeThi TimTheoId(int idDeThi)
        {
            string query = string.Format("SELECT * FROM DeThi WHERE MaDT=@MaDT");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaDT", idDeThi)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            DeThi DeThi = new DeThi();
            while (read.Read())
            {
                DeThi = DoiTuong(read);
            }
            return DeThi;
        }

        public static DeThi DoiTuong(IDataRecord read)
        {
            return new DeThi
            {
                MaDT = Methods.ConvertNumber(read["MaDT"].ToString()),
                TenDT = Methods.ConvertString(read["TenDT"].ToString()),
                MaCD = Methods.ConvertNumber(read["MaCD"].ToString()),
                DoKho = Methods.ConvertString(read["DoKho"].ToString()),
                SoCauHoi = Methods.ConvertNumber(read["SoCauHoi"].ToString()),
                ThoiGian = Methods.ConvertNumber(read["ThoiGian"].ToString()),
                NgayTao = Methods.ConvertDateTime(read["NgayTao"].ToString()),
                DaThi = Methods.ConvertBoolean(read["DaThi"].ToString())
            };
        }

        public static IList<DeThi> LayDanhSach(string chuDe)
        {
            IList<DeThi> danhSach = new List<DeThi>();
            if (chuDe != "Tổng hợp")
            {
                string query = string.Format("SELECT DeThi.* FROM DeThi, ChuDe WHERE DeThi.MaCD = ChuDe.MaCD " +
                    "AND TenCD=@TenCD AND DaThi='True'");
                var parameter = new Collection<SqlParameter>
                {
                    new SqlParameter("@TenCD", chuDe)
                };
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    danhSach.Add(DoiTuong(read));
                }
                return danhSach;
            }
            else
            {
                string query = string.Format("SELECT DeThi.* FROM DeThi, ChuDe WHERE DeThi.MaCD = ChuDe.MaCD " +
                    " AND DaThi='True'");
                var parameter = new Collection<SqlParameter>
                {
                    new SqlParameter("@TenCD", chuDe)
                };
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    danhSach.Add(DoiTuong(read));
                }
                return danhSach;
            }
        }

        public static IList<DeThi> LayDanhSach()
        {
            IList<DeThi> danhSach = new List<DeThi>();
            string query = string.Format("SELECT * FROM DeThi");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                danhSach.Add(DoiTuong(read));
            }
            return danhSach;
        }

        public static DataTable LayDanhSachTheoChuDe()
        {
            DataTable danhSach = new DataTable();
            string query = string.Format("SELECT DeThi.*, TenCD FROM DeThi, ChuDe WHERE DeThi.MaCD=ChuDe.MaCD");
            danhSach = SqlHelper.ExecuteQuery(query);
            return danhSach;
        }

        //public static IList<DeThi> LayDanhSachTinHocCanBan()
        //{
        //    IList<DeThi> danhSach = new List<DeThi>();
        //    string query = string.Format("SELECT DeThi.* FROM DeThi, ChuDe " +
        //        "WHERE DeThi.MaCD = ChuDe.MaCD AND TenCD = N'Tin học căn bản'");
        //    IDataReader read = SqlHelper.ExecuteReader(query);
        //    while (read.Read())
        //    {
        //        danhSach.Add(DoiTuong(read));
        //    }
        //    return danhSach;
        //}

        public static bool Them(DeThi deThi)
        {
            string query = string.Format("INSERT INTO DeThi(TenDT, MaCD, DoKho, SoCauHoi, ThoiGian, NgayTao, DaThi) " +
            "VALUES(@TenDT, @MaCD, @DoKho, @SoCauHoi, @ThoiGian, @NgayTao, @DaThi)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenDT", deThi.TenDT),
                new SqlParameter("@MaCD", deThi.MaCD),
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@ThoiGian", deThi.ThoiGian),
                new SqlParameter("@NgayTao", deThi.NgayTao),
                new SqlParameter("@DaThi", deThi.DaThi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Sua(DeThi deThi)
        {
            string query = string.Format("UPDATE DeThi SET TenDT=@TenDT, MaCD=@MaCD, DoKho=@DoKho, " +
            "SoCauHoi=@SoCauHoi, ThoiGian=@ThoiGian, DaThi=@DaThi WHERE MaDT=@MaDT");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaDT", deThi.MaDT),
                new SqlParameter("@TenDT", deThi.TenDT),
                new SqlParameter("@MaCD", deThi.MaCD),
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@ThoiGian", deThi.ThoiGian),
                new SqlParameter("@DaThi", deThi.DaThi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Xoa(int idDeThi)
        {
            string query = string.Format("DELETE FROM DeThi_CauHoi WHERE MaDT=@MaDT; DELETE FROM DeThi WHERE MaDT=@MaDT");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaDT", idDeThi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }
    }
}